'use strict';

const {
	join
} = require('path');

const Service = require('egg').Service;

class HomeService extends Service {
	async getTotal() {
		const dts_goods = await this.app.mysql.count('dts_goods');
		const dts_order = await this.app.mysql.count('dts_order');
		const dts_goods_product = await this.app.mysql.count('dts_goods_product');
		const dts_user = await this.app.mysql.count('dts_user');
		return {
			"goodsTotal": dts_goods,
			"userTotal": dts_user,
			"productTotal": dts_goods_product,
			"orderTotal": dts_order
		};
	}

	async getChart() {
		const data = await this.app.mysql.query(`select pc.name as name,sum(og.price) as value from dts_order_goods og
		join dts_goods g on g.id = og.goods_id
		join dts_category c on g.category_id = c.id
		join dts_category pc on pc.id = c.pid
		where og.deleted=0
		group by pc.name`);
		const categoryname = [];
		data.forEach(item => {
			categoryname.push(item.category_name)
		})
		var date = new Date();
		var year = date.getFullYear();
		var month = date.getMonth() + 1;
		var months = [];
		for (var i = 1; i <= month; i++) {
			var newmonths = i > 9 ? i : '0' + i;
			months.push(year + '-' + newmonths)
		}
		const usernum = await this.app.mysql.query(`SELECT YEAR(add_time) AS order_year,
		MONTH(add_time) AS order_month,
		COUNT(*) AS total_orders
		FROM dts_user
		WHERE YEAR(add_time) = ${year}
		GROUP BY order_year, order_month
		ORDER BY order_year, order_month;`);
		var userCnt = [];
		usernum.forEach(item => {
			userCnt.push(item.total_orders)
		})

		const ordernum = await this.app.mysql.query(`SELECT YEAR(add_time) AS order_year,
		MONTH(add_time) AS order_month,
		COUNT(*) AS total_orders,
		sum(price) AS price,
		sum(price) AS price
		FROM dts_order_goods
		WHERE YEAR(add_time) = ${year}
		GROUP BY order_year, order_month
		ORDER BY order_year, order_month;`);
		var orderCnt = [];
		var orderPrice = [];
		ordernum.forEach(item => {
			orderCnt.push(item.total_orders);
			orderPrice.push(item.price)
		})


		return {
			categorySell: {
				categoryNames: categoryname,
				categorySellData: data
			},
			"orderAmts": {
				"dayData": months,
				"orderAmtData": orderPrice,
				"orderCntData": orderCnt
			},
			"userOrderCnt": {
				"dayData": months,
				"userCnt": userCnt,
				"orderCnt": orderCnt
			}
		};
	}


	async getuser(parameter) {
		const userlist = await this.app.mysql.query(
			`SELECT *,
		  user_level AS userLevel,
		last_login_time AS lastLoginTime,
		last_login_ip AS lastLoginIp,
		add_time AS addTime,
		update_time AS updateTime,
		share_user_id AS shareUserId,
		weixin_openid AS weixinOpenid FROM dts_user ORDER BY add_time DESC LIMIT ${parameter.limit} OFFSET ${(parameter.page-1)*parameter.limit};`
		);
		const Total = await this.app.mysql.query(`SELECT COUNT(*) AS total_orders FROM dts_user;`);
		return {
			"items": userlist,
			"total": Total[0].total_orders
		};
	}

	async approveAgency(parameter) {
		console.log('-----------', parameter);
		var date = new Date().toLocaleString().replace(new RegExp("/", "g"), "-");
		const status = await this.app.mysql.query(
			`UPDATE dts_user SET status = 0, user_level = 2, update_time= '${date}' WHERE id = ${parameter.userId};`
		);
		if (status.affectedRows == 1) {
			const userpara = await this.app.mysql.query(
				`select id FROM dts_user_account WHERE user_id =  ${parameter.userId};`
			);
			console.log('1111111111', userpara);
			if (userpara.length > 0) {
				const sc = await this.app.mysql.query(
					`UPDATE dts_user_account SET settlement_rate = '${parameter.settlementRate}',update_time= '${date}' WHERE id = ${userpara[0].id};`
				);
				console.log('1111111111', sc);
				return true;
			} else {
				var para = {
					user_id: parameter.userId,
					remain_amount: 0,
					total_amount: 0,
					create_time: date,
					settlement_rate: parameter.settlementRate,
					status: 1,
				}
				const success = await this.app.mysql.insert('dts_user_account', para);
				console.log('-----------', success);
				if (success.affectedRows == 1) {
					return true;
				} else {
					return false;
				}
			}
		}
		return false;
	}

	async getaddress(parameter) {
		const userlist = await this.app.mysql.query(
			`SELECT *,
		  user_id AS userId,
		province_id AS provinceId,
		city_id AS cityId,
		area_id AS areaId,
		is_default AS isDefault,
		add_time AS addTime,
		update_time AS updateTime FROM dts_address ORDER BY add_time DESC LIMIT ${parameter.limit} OFFSET ${(parameter.page-1)*parameter.limit};`
		);
		const Total = await this.app.mysql.query(`SELECT COUNT(*) AS total_orders FROM dts_address;`);
		return {
			"items": userlist,
			"total": Total[0].total_orders
		};
	}

	async collectlist(parameter) {
		const userlist = await this.app.mysql.query(
			`select og.id as id,og.add_time as addTime,g.id as goodsId,g.pic_url as picUrl,g.name as goodsName,u.id as userId,
		u.username as username,u.avatar as avatar
		 from dts_collect og
		join dts_goods g on g.id = og.value_id
		join dts_user u on u.id = og.user_id ORDER BY og.add_time DESC LIMIT ${parameter.limit} OFFSET ${(parameter.page-1)*parameter.limit};`
		);
		const Total = await this.app.mysql.query(`select COUNT(*) AS total_orders
			 from dts_collect og
			join dts_goods g on g.id = og.value_id
			join dts_user u on u.id = og.user_id;`);
		return {
			"items": userlist,
			"total": Total[0].total_orders
		};
	}

	async footprint(parameter) {
		const footprintlist = await this.app.mysql.query(
			`select og.id as id,og.add_time as addTime,g.id as goodsId,g.pic_url as picUrl,g.name as goodsName,u.id as userId,
		u.username as username,u.avatar as avatar
		 from dts_footprint og
		join dts_goods g on g.id = og.goods_id
		join dts_user u on u.id = og.user_id ORDER BY og.add_time DESC LIMIT ${parameter.limit} OFFSET ${(parameter.page-1)*parameter.limit};`
		);
		const Total = await this.app.mysql.query(`select COUNT(*) AS total_orders
			from dts_footprint og
			join dts_goods g on g.id = og.goods_id
			join dts_user u on u.id = og.user_id;`);
		return {
			"items": footprintlist,
			"total": Total[0].total_orders
		};
	}

	async searchhistoryt(parameter) {
		const historytlist = await this.app.mysql.query(
			`select uh.id as id,uh.add_time as addTime,uh.keyword as keyword,u.id as userId,
		u.username as username,u.avatar as avatar
		 from dts_search_history uh
		join dts_user u on u.id = uh.user_id ORDER BY uh.${parameter.sort} ${parameter.order} LIMIT ${parameter.limit} OFFSET ${(parameter.page-1)*parameter.limit};`
		);
		const Total = await this.app.mysql.query(`select COUNT(*) AS total_orders
			from dts_search_history uh
			join dts_user u on u.id = uh.user_id;`);
		return {
			"items": historytlist,
			"total": Total[0].total_orders
		};
	}

	async feedbacklist(parameter) {
		const feedbacklist = await this.app.mysql.query(
			`select * from dts_feedback  ORDER BY ${parameter.sort} ${parameter.order} LIMIT ${parameter.limit} OFFSET ${(parameter.page-1)*parameter.limit};`
		);
		const Total = await this.app.mysql.query(`select COUNT(*) AS total_orders from dts_feedback;`);
		console.log('============', Total)
		return {
			"items": feedbacklist,
			"total": Total[0].total_orders
		};
	}

	delay(ms) {
		console.log('-------', ms)
		return new Promise(resolve => {
			setTimeout(() => {
				resolve(ms)
			}, ms)
		});
	}

	convertToTree(regions, rootId = "0") {
		return regions.filter(item => item.pid == rootId).map(item => (item.children = this.convertToTree(regions,
			item.id), item))
	}

	async regionlist(parameter) {
		const data = await this.app.mysql.select(`dts_region`);
		var list = this.convertToTree(data, 0);
		return list;
	}

	async brandlist(parameter) {
		console.log(
			`select * from dts_brand  ORDER BY ${parameter.sort} ${parameter.order} LIMIT ${parameter.limit} OFFSET ${(parameter.page-1)*parameter.limit};`
		)
		const feedbacklist = await this.app.mysql.query(
			`select * from dts_brand  ORDER BY ${parameter.sort} ${parameter.order} LIMIT ${parameter.limit} OFFSET ${(parameter.page-1)*parameter.limit};`
		);
		const Total = await this.app.mysql.query(`select COUNT(*) AS total_orders from dts_brand;`);
		console.log('============', Total)
		return {
			"items": feedbacklist,
			"total": Total[0].total_orders
		};
	}

	async categorylist(parameter) {
		// console.log(
		// 	`select * from dts_category  ORDER BY ${parameter.sort} ${parameter.order} LIMIT ${parameter.limit} OFFSET ${(parameter.page-1)*parameter.limit};`
		// )
		const list = await this.app.mysql.query(
			`select * from dts_category  ORDER BY ${parameter.sort} ${parameter.order}`
		);
		// const Total = await this.app.mysql.query(`select COUNT(*) AS total_orders from dts_category;`);
		// console.log('============', Total)
		return list;
	}

	async orderlist(parameter) {
		console.log(
			`select * from dts_order  ORDER BY ${parameter.sort} ${parameter.order} LIMIT ${parameter.limit} OFFSET ${(parameter.page-1)*parameter.limit};`
		)
		const feedbacklist = await this.app.mysql.query(
			`select * from dts_order  ORDER BY ${parameter.sort} ${parameter.order} LIMIT ${parameter.limit} OFFSET ${(parameter.page-1)*parameter.limit};`
		);
		const Total = await this.app.mysql.query(`select COUNT(*) AS total_orders from dts_order;`);
		console.log('============', Total)
		return {
			"items": feedbacklist,
			"total": Total[0].total_orders
		};
	}

	async orderdetail(parameter) {
		try {
			const orderifo = await this.app.mysql.query(`select * from dts_order where id = ${parameter.id};`);
			const orderGoods = await this.app.mysql.query(
				`select * from dts_order_goods where order_id = ${parameter.id};`);
			const user = await this.app.mysql.query(`select * from dts_user where id = ${orderifo[0].user_id};`);
			return {
				"order": orderifo[0],
				"orderGoods": orderGoods,
				"user": user[0]
			};
		} catch (e) {
			return e;
		}
	}

	async createissue(parameter) {
		console.log(`parameter`, parameter)
		parameter.add_time = new Date().toLocaleString();
		const insert = await this.app.mysql.insert('dts_issue', parameter);
		console.log('============', insert)
		if (insert.affectedRows == 1) {
			return '';
		}
	}

	async issuelist(parameter) {
		console.log(
			`select * from dts_issue  ORDER BY ${parameter.sort} ${parameter.order} LIMIT ${parameter.limit} OFFSET ${(parameter.page-1)*parameter.limit};`
		)
		const feedbacklist = await this.app.mysql.query(
			`select * from dts_issue  ORDER BY ${parameter.sort} ${parameter.order} LIMIT ${parameter.limit} OFFSET ${(parameter.page-1)*parameter.limit};`
		);
		const Total = await this.app.mysql.query(`select COUNT(*) AS total_orders from dts_issue;`);
		console.log('============', Total)
		return {
			"items": feedbacklist,
			"total": Total[0].total_orders
		};
	}

	async updateissue(parameter) {
		console.log(`parameter`, parameter)
		const update = await this.app.mysql.update('dts_issue', parameter, {
			where: {
				id: parameter.id
			} //修改查询条件
		});
		console.log('============', update)
		if (update.affectedRows == 1) {
			return '';
		}
		// return {
		// 	"items": feedbacklist,
		// 	"total": Total[0].total_orders
		// };
	}

	async deleteissue(parameter) {
		console.log(`parameter`, parameter)
		const update = await this.app.mysql.query(`delete from dts_issue where id = ${parameter.id}`);
		console.log('============', update)
		if (update.affectedRows == 1) {
			return '';
		}
		// return {
		// 	"items": feedbacklist,
		// 	"total": Total[0].total_orders
		// };
	}

	async keywordlist(parameter) {
		console.log(
			`select * from dts_keyword  ORDER BY ${parameter.sort} ${parameter.order} LIMIT ${parameter.limit} OFFSET ${(parameter.page-1)*parameter.limit};`
		)
		const feedbacklist = await this.app.mysql.query(
			`select * from dts_keyword  ORDER BY ${parameter.sort} ${parameter.order} LIMIT ${parameter.limit} OFFSET ${(parameter.page-1)*parameter.limit};`
		);
		const Total = await this.app.mysql.query(`select COUNT(*) AS total_orders from dts_keyword;`);
		console.log('============', Total)
		return {
			"items": feedbacklist,
			"total": Total[0].total_orders
		};
	}

	async goodslist(parameter) {
		const feedbacklist = await this.app.mysql.query(
			`select * from dts_goods  ORDER BY ${parameter.sort} ${parameter.order} LIMIT ${parameter.limit} OFFSET ${(parameter.page-1)*parameter.limit};`
		);
		const Total = await this.app.mysql.query(`select COUNT(*) AS total_orders from dts_goods;`);
		console.log('============', Total)
		return {
			"items": feedbacklist,
			"total": Total[0].total_orders
		};
	}
	
	convertToTreeone(regions, rootId = "0") {
		return regions.filter(item => item.pid == rootId).map(item => (item.children = this.convertToTreeone(regions,
			item.value), item))
	}
	
	async catAndBrand(parameter) {
		const category = await this.app.mysql.query('SELECT dts_category.id AS value,dts_category.pid, dts_category.`name` AS label FROM dts_category');
		const brand = await this.app.mysql.query(`SELECT dts_brand.id AS value, dts_brand.name AS label FROM dts_brand`);
		var categoryTree = this.convertToTreeone(category, 0);
		categoryTree.forEach(item=>{
			if(item.children.length>0){
				item.children.forEach(zitem=>{
					if(zitem.children.length == 0){
						delete zitem.children;
					}
				})
			}else{
				delete item.children;
			}
		})
		
		return {
			"categoryList": categoryTree,
			"brandList": brand,
			"isAdmin": true
		};
	}
	
	async creategoods(parameter) {
		const category = await this.app.mysql.query('SELECT dts_category.id AS value,dts_category.pid, dts_category.`name` AS label FROM dts_category');
		const brand = await this.app.mysql.query(`SELECT dts_brand.id AS value, dts_brand.name AS label FROM dts_brand`);
		var categoryTree = this.convertToTreeone(category, 0);
		categoryTree.forEach(item=>{
			if(item.children.length>0){
				item.children.forEach(zitem=>{
					if(zitem.children.length == 0){
						delete zitem.children;
					}
				})
			}else{
				delete item.children;
			}
		})
		
		return {
			"categoryList": categoryTree,
			"brandList": brand,
			"isAdmin": true
		};
	}

	async commentlist(parameter) {
		// const feedbacklist = await this.app.mysql.query(
		// 	`select * from dts_comment  ORDER BY ${parameter.sort} ${parameter.order} LIMIT ${parameter.limit} OFFSET ${(parameter.page-1)*parameter.limit};`
		// );
		// const Total = await this.app.mysql.query(`select COUNT(*) AS total_orders from dts_comment;`);
		var Comment = this.ctx.model.Comment;
		var User = this.ctx.model.User;
		var Goods = this.ctx.model.Goods;
		//hasMany一对多
		// DtsOrder.hasMany(DtsOrderGoods, {
		// 	foreginkey: "order_id",
		// 	targetkey: "id",
		// })
		//belongsTo 一对一
		Comment.belongsTo(User, {
			foreignKey: "user_id"
		})
		Comment.belongsTo(Goods, {
			foreignKey: 'value_id'
		})

		let result = await Comment.findAll({
			// attributes: [ 'id', 'user_id' ],
			include: [User, Goods],
			limit: Number(parameter.limit),
			offset: Number((parameter.page - 1) * parameter.limit),
			order: [
				[parameter.sort, parameter.order] // 按用户名称升序排列
			]
		})

		const Total = await this.app.mysql.query(`select COUNT(*) AS total_orders from dts_comment;`);
		return {
			"items": result,
			"total": Total[0].total_orders
		};
	}

	async commentdelete(parameter) {
		var xiugai = {
			deleted: 1
		}
		const feedbacklist = await this.app.mysql.update('dts_comment', xiugai, {
			where: {
				id: parameter.id
			}
		});
		return '';
	}

	async articlelist(parameter) {
		var Article = this.ctx.model.Article;
		console.log('===========', Article)
		const list = await Article.findAll({
			limit: Number(parameter.limit),
			offset: Number((parameter.page - 1) * parameter.limit),
			order: [
				[parameter.sort, parameter.order] // 按用户名称升序排列
			]
		})

		const Total = await this.app.mysql.query(`select COUNT(*) AS total_orders from dts_article;`);
		return {
			"items": list,
			"total": Total[0].total_orders
		};
	}

	async articledetail(parameter) {
		var Article = this.ctx.model.Article;
		console.log('=========', parameter.id)
		const detail = await Article.findOne({
			where: {
				id: parameter.id
			}
		})

		return detail;
	}

	async adlist(parameter) {
		var list = await this.ctx.model.Advertisement.findAll({
			limit: Number(parameter.limit),
			offset: Number((parameter.page - 1) * parameter.limit),
			order: [
				[parameter.sort, parameter.order]
			]
		})
		const Total = await this.app.mysql.query(`select COUNT(*) AS total_orders from dts_ad;`);
		return {
			"items": list,
			"total": Total[0].total_orders
		};
	}

	async createAd(parameter) {
		parameter.addTime = new Date();
		console.log('=========', parameter)
		var status = await this.ctx.model.Advertisement.create(parameter);
		console.log('=========', status)
		return '';
	}

	async deleteAd(parameter) {
		var status = await this.ctx.model.Advertisement.destroy({
			where: {
				id: parameter.id
			}
		});
		return '';
	}

	async updateAd(parameter) {
		var newdate = new Date().toLocaleString();
		var status = await this.app.mysql.query(
			`update dts_ad set name = '${parameter.name}',content = '${parameter.content}',link = '${parameter.link}',position = ${parameter.position},update_time = '${newdate}' where id = ${parameter.id}`
		)
		return '';
	}

	async couponlist(parameter) {
		var list = await this.ctx.model.Coupon.findAll({
			limit: Number(parameter.limit),
			offset: Number((parameter.page - 1) * parameter.limit),
			order: [
				[parameter.sort, parameter.order]
			]
		})
		const Total = await this.app.mysql.query(`select COUNT(*) AS total_orders from dts_coupon;`);
		return {
			"items": list,
			"total": Total[0].total_orders
		};
	}

	async couponread(parameter) {
		var detail = await this.ctx.model.Coupon.findOne({
			where: {
				id: parameter.id
			}
		})
		return detail;
	}

	async couponlistuser(parameter) {
		var CouponUser = this.ctx.model.CouponUser;
		var User = this.ctx.model.User;
		CouponUser.belongsTo(User, {
			foreignKey: 'user_id'
		})

		let list = await CouponUser.findAll({
			// attributes: [ 'id', 'user_id' ],
			include: [{
				model: User,
				attributes: ['username', 'avatar'],
			}],
			where: {
				coupon_id: parameter.couponId
			},
			limit: Number(parameter.limit),
			offset: Number((parameter.page - 1) * parameter.limit),
			order: [
				[parameter.sort, parameter.order] // 按用户名称升序排列
			]
		})

		const Total = await this.app.mysql.query(
			`select COUNT(*) AS total_orders from dts_coupon_user where coupon_id = ${parameter.couponId};`);
		return {
			"items": list,
			"total": Total[0].total_orders
		};
	}

	async topiclist(parameter) {
		var list = await this.ctx.model.Topic.findAll({
			limit: Number(parameter.limit),
			offset: Number((parameter.page - 1) * parameter.limit),
			order: [
				[parameter.sort, parameter.order]
			]
		})
		const Total = await this.app.mysql.query(`select COUNT(*) AS total_orders from dts_topic;`);
		return {
			"items": list,
			"total": Total[0].total_orders
		};
	}

	async grouponlistRecord(parameter) {
		var Groupon = this.ctx.model.Groupon;
		var DtsOrderGoods = this.ctx.model.DtsOrderGoods;
		var User = this.ctx.model.User;

		Groupon.hasMany(DtsOrderGoods, {
			foreignKey: "order_id",
			sourceKey: "order_id",
		})

		Groupon.belongsTo(User, {
			foreignKey: "user_id"
		})

		let list = await Groupon.findAll({
			// attributes: [ 'id', 'order_id' ],
			include: [{
				model: DtsOrderGoods,
				// attributes: [ 'goods_name'],
			}, {
				model: User,
				attributes: ['username', 'avatar', 'mobile'],
			}],
			limit: Number(parameter.limit),
			offset: Number((parameter.page - 1) * parameter.limit),
			order: [
				[parameter.sort, parameter.order]
			]
		})

		const Total = await this.app.mysql.query(`select COUNT(*) AS total_orders from dts_groupon;`);
		return {
			"items": list,
			"total": Total[0].total_orders
		};
	}

	async grouponlist(parameter) {
		var GrouponRrules = this.ctx.model.GrouponRrules;


		let list = await GrouponRrules.findAll({
			// attributes: [ 'id', 'order_id' ],
			limit: Number(parameter.limit),
			offset: Number((parameter.page - 1) * parameter.limit),
			order: [
				[parameter.sort, parameter.order]
			]
		})

		const Total = await this.app.mysql.query(`select COUNT(*) AS total_orders from dts_groupon_rules;`);
		return {
			"items": list,
			"total": Total[0].total_orders
		};
	}

	async creategroupon(parameter) {
		// var status = await this.ctx.model.GrouponRrules.create(parameter);
		var data = {
			goods_id: parameter.goodsId,
			discount: parameter.discount,
			discount_member: parameter.discountMember,
			expire_time: parameter.expireTime,
			goods_name: parameter.goods_name,
			pic_url: parameter.pic_url,
			add_time: new Date().toLocaleString()
		}
		console.log('-------', data)
		const insert = await this.app.mysql.insert('dts_groupon_rules', data);
		return '';
	}



	async menulist(parameter) {
		var Menu = this.ctx.model.Menu;
		var Buttonbox = this.ctx.model.Buttonbox;

		Menu.hasMany(Buttonbox, {
			foreignKey: "menu_id",
			sourceKey: "id",
		})

		let list = await Menu.findAll({
			// attributes: [ 'id', 'order_id' ],
			include: [{
				model: Buttonbox,
				// attributes: ['id', 'name', 'menuId', 'identification'],
			}]
		})


		return list;
	}

	async createmenu(parameter) {
		parameter.add_time = new Date().toLocaleString();
		var buttons = parameter.buttonboxes.length > 0 ? JSON.parse(JSON.stringify(parameter.buttonboxes)) : [];
		delete parameter.buttonboxes;
		const data = await this.app.mysql.insert(`dts_menu`, parameter);
		if (buttons.length > 0) {
			buttons.forEach(item => {
				item.add_time = new Date().toLocaleString();
				item.menuId = data.insertId;
			})
			console.log('=========', buttons)
			var Buttonbox = this.ctx.model.Buttonbox;
			const createdUsers = await Buttonbox.bulkCreate(buttons);

		}
		// 使用bulkCreate一次性添加到数据库
		// const createdUsers = await User.bulkCreate(users);
		return '添加成功';
	}

	async updatemenu(parameter) {
		// parameter.add_time = new Date(parameter.add_time).toLocaleString();
		parameter.update_time = new Date().toLocaleString();
		var buttons = parameter.buttonboxes.length > 0 ? JSON.parse(JSON.stringify(parameter.buttonboxes)) : [];
		delete parameter.buttonboxes;
		delete parameter.addTime;
		delete parameter.updateTime;
		const data = await this.app.mysql.update(`dts_menu`, parameter, {
			where: {
				id: parameter.id
			}
		});
		if (buttons.length > 0) {
			var updatelist = [];
			var addlist = [];
			buttons.forEach(item => {
				if (item.id) {
					item.updateTime = new Date().toLocaleString();
					updatelist.push(item)
				} else {
					item.addTime = new Date().toLocaleString();
					item.menuId = parameter.id;
					addlist.push(item)
				}

			})
			var Buttonbox = this.ctx.model.Buttonbox;

			if (updatelist.length > 0) {
				console.log('=========', updatelist)
				await Promise.all(
					updatelist.map((item) => {
						return Buttonbox.update(
							item, // 更新的字段
							{
								where: {
									id: item.id
								}
							} // 更新条件
						);
					})
				);
			}
			if (addlist.length > 0) {
				console.log('=========', addlist)
				await Buttonbox.bulkCreate(addlist);
			}

		}
		return '修改成功';
	}

	async deletemenu(parameter) {
		const data = await this.app.mysql.delete(`dts_menu`, {
			id: parameter.id
		});
		return '删除成功';
	}

	async adminlist(parameter) {
		var list = await this.ctx.model.Admin.findAll({
			limit: Number(parameter.limit),
			offset: Number((parameter.page - 1) * parameter.limit),
			order: [
				[parameter.sort, parameter.order]
			]
		})
		const Total = await this.app.mysql.query(`select COUNT(*) AS total_orders from dts_admin;`);
		return {
			"items": list,
			"total": Total[0].total_orders
		};
	}

	async createadmin(parameter) {
		parameter.add_time = new Date().toLocaleString();
		parameter.role_ids = parameter.roleIds.join();
		delete parameter.roleIds;
		const ins = await this.app.mysql.insert(`dts_admin`, parameter);
		return '添加成功';
	}
	async updateadmin(parameter) {
		var updatedata = {
			"update_time": new Date().toLocaleString(),
			"username": parameter.username,
			"password": parameter.password,
			"avatar": parameter.avatar,
			"role_ids": parameter.roleIds.join()
		}
		const ins = await this.app.mysql.update(`dts_admin`, updatedata, {
			where: {
				id: parameter.id
			}
		});
		return '修改成功';
	}
	
	async rolelist(parameter) {
		
		var list = await this.app.mysql.query(`SELECT Role.id, Role.name, Role.desc, Role.enabled, Role.add_time AS addTime, Role.update_time AS updateTime,  permission.buttonids AS Buttonids,  RoleMenu.menuids AS Menuids FROM dts_role AS Role LEFT OUTER JOIN dts_role_permission AS permission ON Role.id = permission.role_id LEFT OUTER JOIN dts_role_menu AS RoleMenu ON Role.id = RoleMenu.role_id ORDER BY Role.${parameter.sort} ${parameter.order} LIMIT ${parameter.limit} OFFSET ${(parameter.page-1)*parameter.limit};`)
		// var Role = this.ctx.model.Role;
		// var Permission = this.ctx.model.Permission;
		// var RoleMenu = this.ctx.model.RoleMenu;
		
		// Role.belongsTo(Permission, {
		// 	foreignKey: "id", //主表
		// 	targetKey: 'roleId' //副表
		// })
		
		// Role.belongsTo(RoleMenu, {
		// 	foreignKey: "id", //主表
		// 	targetKey: 'roleId' //副表
		// })
		
		// let list = await Role.findAll({
		// 	attributes: [ 'id', 'order_id' ],
		// 	include: [{
		// 		model: Permission,
		// 		attributes: [ 'buttonids' ]
		// 	},{
		// 		model: RoleMenu,
		// 		attributes: [ 'menuids' ]
		// 	}],
		// 	limit: Number(parameter.limit),
		// 	offset: Number((parameter.page - 1) * parameter.limit),
		// 	order: [
		// 		[parameter.sort, parameter.order]
		// 	]
		// })
		
		
		const Total = await this.app.mysql.query(`select COUNT(*) AS total_orders from dts_role;`);
		return {
			"items": list,
			"total": Total[0].total_orders
		};
	}
	
	async roledelete(parameter) {
		console.log('=========',parameter)
		if(parameter.Menuids){
			await this.app.mysql.delete(`dts_role_menu`, {
				role_id: parameter.id
			});
		}
		if(parameter.Buttonids){
			await this.app.mysql.delete(`dts_role_permission`, {
				role_id: parameter.id
			});
		}
		const data = await this.app.mysql.delete(`dts_role`, {
			id: parameter.id
		});
		return '删除成功';
	}
	
	
	async rolecreate(parameter) {
		var adddata = {
			name: parameter.name,
			desc: parameter.desc,
			add_time: new Date().toLocaleString()
		}
		const role = await this.app.mysql.insert(`dts_role`, adddata);
		if(parameter.Menuids){
			var menuadd = {
				role_id: role.insertId,
				menuids: parameter.Menuids,
				add_time: new Date().toLocaleString()
			}
			await this.app.mysql.insert(`dts_role_menu`, menuadd);
		}
		if(parameter.Buttonids){
			var buttons = {
				role_id: role.insertId,
				buttonids: parameter.Buttonids,
				add_time: new Date().toLocaleString()
			}
			await this.app.mysql.insert(`dts_role_permission`, buttons);
		}
		
		return '添加成功';
	}
	
	async roleupdate(parameter) {
		var adddata = {
			name: parameter.name,
			desc: parameter.desc,
			update_time: new Date().toLocaleString()
		}
		const role = await this.app.mysql.update(`dts_role`, adddata,{
			where: {
				id: parameter.id
			}
		});
		if(parameter.Menuids){
			var menuadd = {
				menuids: parameter.Menuids,
				update_time: new Date().toLocaleString()
			}
			await this.app.mysql.update(`dts_role_menu`, menuadd,{
				where: {
					role_id: parameter.id
				}
			});
		}
		if(parameter.Buttonids){
			var buttons = {
				buttonids: parameter.Buttonids,
				update_time: new Date().toLocaleString()
			}
			await this.app.mysql.update(`dts_role_permission`, buttons,{
				where: {
					role_id: parameter.id
				}
			});
		}
		
		return '编辑成功';
	}
	
	
	async datalist(parameter) {
		const Total = await this.app.mysql.query(`select COUNT(*) AS total_orders from data_list;`);
		var list = await this.ctx.model.List.findAll({
			limit: Number(parameter.limit),
			offset: Number((parameter.page - 1) * parameter.limit),
			order: [
				[parameter.sort, parameter.order]
			]
		})
		return {
			"list": list,
			"total": Total[0].total_orders
		};
	}
}

module.exports = HomeService;